Microsoft Excel Help

[SoundofSilence]

* I posted this a while back, but never actually got a method that I could figure out to work, so I'm posting again. Thanks guys *

Short description: In MS Excel, I've got a list of "A" values, and their corresponding "B" values -- how would I go about generating one number or formula that is a "best fit" for using value "A" to determine value "B" ?

Long description: Basically what I'm wanting to do in a handicapping sense is, I've got the # of hits a team had in a season, and the # of runs they produced that same season. I have this data for multiple seasons. What I want Excelt to generate is, a multiplier or formula that i can plug the "# hits" value in, and get a "best fit" to the runs produced value.

I want to be able to use this same formula for all of the season hit totals I have, and have it churn out the number of runs produced. I realize that there will be no perfect formula, or one that is even always within 5-10 runs -- I know there will be large discrepancies, but I'm looking for the "best fit" formula that comes closest to all of them.

Quick disclaimer -- no, I am not using just the # of hits to determine how many runs a team will score, my database will be far more complicated than that, but the methodology behind finding that # will be used on different variables in my database quite a bit, so I figured I would start out with the simple example and hope to find out how to do it.

Thanks in advance guys!

 

[Gridster]

Sounds like you want to determine the runs/hit ratio for some point in the future based on runs/hit ratios from the past. One way to do it is with a curve fitting formula. Say you've got 3 seasons charted. Let x = 1 for the first season, 2 for 2nd and 3 for 3rd, and corresponding y values the runs/hit ratios which are known. Determine the function that draws a curve through the runs/hit ratios and plot 4 (or any point you're interested in) along that line to determine your new runs/hit value. Then plug in your hits to determine the runs produced. If you're interested, I'll post the method for determining a curve fitting function that defines a line that goes through any number of previously plotted points.

 

[drunkguy]

I'm no excel expert, but I would say you want the x-axis values (dependent variable) to equal number of hits. Y-axis should plot number of runs (independent variable). Graph the data, and now you can do a curve fit to determine the best-fit line. The line equation will give you the number of runs when you input the number of hits.

 

[DNoonan]

SoS- explain what you mean by "best fit"...from a statistical point of view it sounds like you are talking about the median of a data set.

I work in excel as a career and to help with my handicapping.

I would be happy to discuss/share the system I am developing in excel and help you with your problem if you give me a bit more info or a more detailed example of what you are trying to do.

I can be reached here or at noonan_rx@yahoo.com
and I am not trying to sell anything